﻿use [slqj]

-- =============================================
-- Author:		liangzy
-- Create date: 2011-03-19
-- Description:	 添加视图：View_NewAdministrators
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_NewAdministrators]'))
begin
drop view [View_NewAdministrators];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_NewAdministrators]
AS
SELECT     dbo.Administrators.*, dbo.AuthAdminRole.RoleIDList
FROM         dbo.Administrators LEFT OUTER JOIN
                      dbo.AuthAdminRole ON dbo.Administrators.AdministratorID = dbo.AuthAdminRole.AdministratorID'
go

-- =============================================
-- Author:		liangzy
-- Create date: 2011-03-21
-- Description:	  添加视图：View_City
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_City]'))
begin
drop view [View_City];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_City]
AS
SELECT     dbo.City.CityID, dbo.City.ProvinceID, dbo.City.CityName, dbo.Province.ProvinceName
FROM         dbo.City INNER JOIN
                      dbo.Province ON dbo.City.ProvinceID = dbo.Province.ProvinceID AND dbo.City.ProvinceID = dbo.Province.ProvinceID'
go

-- =============================================
-- Author:		liangzy
-- Create date: 2011-03-21
-- Description:	  添加视图：View_Members
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_Members]'))
begin
drop view [View_Members];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_Members]
AS
SELECT     dbo.City.CityName, dbo.Members.MembersID, dbo.Members.MemberTypeID, dbo.Members.ApproveStatusID, dbo.Members.LoginName, dbo.Members.Password, 
                      dbo.Members.MemberName, dbo.Members.CompanyName, dbo.Members.Contact, dbo.Members.IDNO, dbo.Members.ProvinceID, dbo.Members.CityID, 
                      dbo.Members.TelePhone, dbo.Members.Email, dbo.Members.Mobile, dbo.Members.Fax, dbo.Members.Address, dbo.Members.ZipCode, dbo.Members.WebSite, 
                      dbo.Members.Description, dbo.Members.LastLogin, dbo.Members.Activity, dbo.Members.MemberStatus, dbo.Members.Creater, dbo.Members.CreatedBy, 
                      dbo.Members.Created, dbo.Members.Modified, dbo.Members.ModifiedBy, dbo.Members.ModifiedByID, dbo.MemberType.MemberTypeName, 
                      dbo.Province.ProvinceName
FROM         dbo.City INNER JOIN
                      dbo.Members ON dbo.City.CityID = dbo.Members.CityID AND dbo.City.CityID = dbo.Members.CityID INNER JOIN
                      dbo.MemberType ON dbo.Members.MemberTypeID = dbo.MemberType.MemberTypeID AND 
                      dbo.Members.MemberTypeID = dbo.MemberType.MemberTypeID INNER JOIN
                      dbo.Province ON dbo.City.ProvinceID = dbo.Province.ProvinceID AND dbo.Members.ProvinceID = dbo.Province.ProvinceID AND 
                      dbo.Members.ProvinceID = dbo.Province.ProvinceID'
go

-- =============================================
-- Author:		liangzy
-- Create date: 2011-03-22
-- Description:	  添加视图：View_Attractions
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_Attractions]'))
begin
drop view [View_Attractions];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_Attractions]
AS
SELECT     dbo.Attractions.*, dbo.AttractionType.AttractionTypeName, dbo.City.CityName, dbo.Province.ProvinceName
FROM         dbo.Attractions INNER JOIN
                      dbo.AttractionType ON dbo.Attractions.AttractionTypeID = dbo.AttractionType.AttractionTypeID INNER JOIN
                      dbo.City ON dbo.Attractions.CityID = dbo.City.CityID INNER JOIN
                      dbo.Province ON dbo.Attractions.ProvinceID = dbo.Province.ProvinceID AND dbo.City.ProvinceID = dbo.Province.ProvinceID
where Attractions.Cityid=city.cityid and Attractions.provinceid=province.provinceid and Attractions.AttractionTypeid=AttractionType.AttractionTypeid'
go
		 
-- =============================================
-- Author:		luohr
-- Create date: 2011-03-25
-- Description:	  添加视图：View_AttractionsTicket
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_AttractionsTicket]'))
begin
drop view [View_AttractionsTicket];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_AttractionsTicket]
AS
SELECT     dbo.Attractions.AttractionsID, dbo.Attractions.AttractionTypeID, dbo.Attractions.ProvinceID, dbo.Attractions.CityID, dbo.Attractions.AttractionStatus, 
                      dbo.Attractions.ApproveStatusID, dbo.Attractions.AttractionsName, dbo.Attractions.Address, dbo.Attractions.Introduction, dbo.Attractions.ImageUrl, 
                      dbo.Attractions.SiteUrl, dbo.Attractions.Description, dbo.Attractions.Sort, dbo.Attractions.Creater, dbo.Attractions.CreatedBy, dbo.Attractions.Created, 
                      dbo.Attractions.Modified, dbo.Attractions.ModifiedBy, dbo.Attractions.ModifiedByID, dbo.AttractionType.AttractionTypeName, 
                      dbo.Province.ProvinceName, dbo.City.CityName, dbo.Province.ProvinceName + '' '' + dbo.City.CityName AS Area, dbo.Tickets.TicketTypeID, 
                      dbo.Tickets.ApproveStatusID AS TicketsApproveStatusID, dbo.Tickets.TicketsName, dbo.Tickets.OriginalPrice, dbo.Tickets.SettlementPrice, 
                      dbo.Tickets.SalesGuidePrice, dbo.Tickets.Discount, dbo.Tickets.BackPoint, dbo.Tickets.TicketStatus, dbo.TicketType.TicketTypeName, 
                      dbo.ApproveStatus.ApproveStatusName, ApproveStatus_1.ApproveStatusName AS TicketsApproveStatusName, dbo.Tickets.TicketsID
FROM         dbo.Attractions INNER JOIN
                      dbo.AttractionType ON dbo.Attractions.AttractionTypeID = dbo.AttractionType.AttractionTypeID INNER JOIN
                      dbo.Province ON dbo.Attractions.ProvinceID = dbo.Province.ProvinceID INNER JOIN
                      dbo.City ON dbo.Attractions.CityID = dbo.City.CityID INNER JOIN
                      dbo.Tickets ON dbo.Attractions.AttractionsID = dbo.Tickets.AttractionsID INNER JOIN
                      dbo.TicketType ON dbo.Tickets.TicketTypeID = dbo.TicketType.TicketTypeID INNER JOIN
                      dbo.ApproveStatus ON dbo.Attractions.ApproveStatusID = dbo.ApproveStatus.ApproveStatusID INNER JOIN
                      dbo.ApproveStatus AS ApproveStatus_1 ON dbo.Tickets.ApproveStatusID = ApproveStatus_1.ApproveStatusID'
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-03-25
-- Description:	  添加视图：View_TopTicketsID
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_TopTicketsID]'))
begin
drop view [View_TopTicketsID];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_TopTicketsID]
AS

SELECT     AttractionsID,
                          (SELECT     TOP (1) TicketsID
                            FROM          dbo.Tickets
                            WHERE      (AttractionsID = dbo.Attractions.AttractionsID) AND (ApproveStatusID = 2) AND (TicketStatus = 1)
                            ORDER BY TicketsID) AS TicketsID
FROM         dbo.Attractions'
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-03-25
-- Description:	  添加视图：View_TopAttractionsTicket
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_TopAttractionsTicket]'))
begin
drop view [View_TopAttractionsTicket];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_TopAttractionsTicket]
AS
SELECT     dbo.View_AttractionsTicket.*
FROM         dbo.View_TopTicketsID INNER JOIN
                      dbo.View_AttractionsTicket ON dbo.View_TopTicketsID.TicketsID = dbo.View_AttractionsTicket.TicketsID'
go

-- =============================================
-- Author:		liangzy
-- Create date: 2011-03-25
-- Description:	  添加视图：View_Order
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_Order]'))
begin
drop view [View_Order];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_Order]
AS
SELECT     dbo.Orders.*, dbo.OrderStatus.OrderStatusName, dbo.OrderType.OrderTypeName
FROM         dbo.Orders INNER JOIN
                      dbo.OrderStatus ON dbo.Orders.OrderStatusID = dbo.OrderStatus.OrderStatusID INNER JOIN
                      dbo.OrderType ON dbo.Orders.OrderTypeID = dbo.OrderType.OrderTypeID'
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-03-30
-- Description:	订单表添加字段
-- =============================================
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='Orders' and b.xtype='u' and a.name='TelePhone')
begin

alter table Orders add TelePhone nvarchar(50) null
end
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='Orders' and b.xtype='u' and a.name='Mobile')
begin

alter table Orders add Mobile nvarchar(50) null
end

-- =============================================
-- Author:		luohr
-- Create date: 2011-03-30
-- Description:	门票订单项表添加字段
-- =============================================
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='TicketOrderItems' and b.xtype='u' and a.name='OrderDate')
begin

alter table TicketOrderItems add OrderDate datetime not null
end
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='TicketOrderItems' and b.xtype='u' and a.name='AttractionsName')
begin

alter table TicketOrderItems add AttractionsName nvarchar(50) null
end
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='TicketOrderItems' and b.xtype='u' and a.name='CityID')
begin

alter table TicketOrderItems add CityID int null
end
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='TicketOrderItems' and b.xtype='u' and a.name='Area')
begin

alter table TicketOrderItems add Area nvarchar(50) null
end

-- =============================================
-- Author:		liangzy
-- Create date: 2011-03-25
-- Description:	  添加视图：View_PriceTax
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_PriceTax]'))
begin
drop view [View_PriceTax];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_PriceTax]
AS
SELECT     dbo.PriceTax.PriceTaxID, dbo.PriceTax.CityID, dbo.PriceTax.MinPrice, dbo.PriceTax.MaxPrice, dbo.PriceTax.Tax, dbo.City.CityID AS Expr1, dbo.City.ProvinceID, 
                      dbo.City.CityName
FROM         dbo.PriceTax INNER JOIN
                      dbo.City ON dbo.PriceTax.CityID = dbo.City.CityID AND dbo.PriceTax.CityID = dbo.City.CityID'
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-03-31
-- Description:	订单表添加字段
-- =============================================
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='Orders' and b.xtype='u' and a.name='TradeNo')
begin

alter table Orders add TradeNo nvarchar(100) null
end
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='Orders' and b.xtype='u' and a.name='PayInfo')
begin

alter table Orders add PayInfo ntext null
end
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='Orders' and b.xtype='u' and a.name='PayDate')
begin

alter table Orders add PayDate datetime null
end

-- =============================================
-- Author:		liangzy
-- Create date: 2011-04-4
-- Description:	  添加视图：View_Order
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_Order]'))
begin
drop view [View_Order];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_Order]
AS
SELECT     dbo.Orders.*, dbo.OrderStatus.OrderStatusName, dbo.OrderType.OrderTypeName
FROM         dbo.Orders INNER JOIN
                      dbo.OrderStatus ON dbo.Orders.OrderStatusID = dbo.OrderStatus.OrderStatusID INNER JOIN
                      dbo.OrderType ON dbo.Orders.OrderTypeID = dbo.OrderType.OrderTypeID'
go

-- =============================================
-- Author:		liangzy
-- Create date: 2011-04-6
-- Description:	  添加视图：View_HotelInfo
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_HotelInfo]'))
begin
drop view [View_HotelInfo];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_HotelInfo]
AS
SELECT     dbo.HotelInfo.*, dbo.City.CityName, dbo.Province.ProvinceName, dbo.ApproveStatus.ApproveStatusName
FROM         dbo.City INNER JOIN
                      dbo.HotelInfo ON dbo.City.CityID = dbo.HotelInfo.CityID INNER JOIN
                      dbo.Province ON dbo.City.ProvinceID = dbo.Province.ProvinceID AND dbo.HotelInfo.ProvinceID = dbo.Province.ProvinceID INNER JOIN
                      dbo.ApproveStatus ON dbo.HotelInfo.ApproveStatusID = dbo.ApproveStatus.ApproveStatusID'
go

-- =============================================
-- Author:		liangzy
-- Create date: 2011-04-6
-- Description:	  添加视图：View_GuestRoom
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_GuestRoom]'))
begin
drop view [View_GuestRoom];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_GuestRoom]
AS
SELECT     dbo.GuestRoom.*, dbo.HotelInfo.HotelName, dbo.ApproveStatus.ApproveStatusName
FROM         dbo.ApproveStatus INNER JOIN
                      dbo.GuestRoom ON dbo.ApproveStatus.ApproveStatusID = dbo.GuestRoom.ApproveStatusID INNER JOIN
                      dbo.HotelInfo ON dbo.GuestRoom.HotelInfoID = dbo.HotelInfo.HotelInfoID'
go

-- =============================================
-- Author:		liangzy
-- Create date: 2011-04-7
-- Description:	  添加视图：View_GuestRoomItem
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_GuestRoomItem]'))
begin
drop view [View_GuestRoomItem];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_GuestRoomItem]
AS
SELECT     dbo.GuestRoomItem.*, dbo.View_GuestRoom.GuestRoomName, dbo.View_GuestRoom.HotelName, 
                      dbo.View_GuestRoom.HotelInfoID
FROM         dbo.GuestRoomItem INNER JOIN
                      dbo.View_GuestRoom ON dbo.GuestRoomItem.GuestRoomID = dbo.View_GuestRoom.GuestRoomID'
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-04-8
-- Description:	  添加视图：View_TopGuestRoomID
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_TopGuestRoomID]'))
begin
drop view [View_TopGuestRoomID];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_TopGuestRoomID]
AS
SELECT     HotelInfoID,
                          (SELECT     TOP (1) GuestRoomID
                            FROM          dbo.GuestRoom
                            WHERE      (HotelInfoID = dbo.HotelInfo.HotelInfoID) AND (ApproveStatusID = 2) AND (dbo.HotelInfo.HotelStatusID = 1)
                            ORDER BY GuestRoomID) AS GuestRoomID
FROM         dbo.HotelInfo'
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-04-8
-- Description:	  添加视图：View_GuestRoomList
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_GuestRoomList]'))
begin
drop view [View_GuestRoomList];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_GuestRoomList]
AS
SELECT     dbo.GuestRoom.*, dbo.View_HotelInfo.ProvinceID, dbo.View_HotelInfo.CityID, 
                      dbo.View_HotelInfo.HotelStatusID, dbo.View_HotelInfo.ApproveStatusID AS HotelApproveStatusID, dbo.View_HotelInfo.HotelName, 
                      dbo.View_HotelInfo.Address, dbo.View_HotelInfo.Introduction, dbo.View_HotelInfo.ImageUrl, dbo.View_HotelInfo.SiteUrl, dbo.View_HotelInfo.TelePhone, 
                      dbo.View_HotelInfo.Fax, dbo.View_HotelInfo.Description, dbo.View_HotelInfo.CityName, dbo.View_HotelInfo.ProvinceName, 
                      dbo.View_HotelInfo.ApproveStatusName AS HotelApproveStatusName, dbo.ApproveStatus.ApproveStatusName
FROM         dbo.GuestRoom INNER JOIN
                      dbo.View_HotelInfo ON dbo.GuestRoom.HotelInfoID = dbo.View_HotelInfo.HotelInfoID INNER JOIN
                      dbo.ApproveStatus ON dbo.GuestRoom.ApproveStatusID = dbo.ApproveStatus.ApproveStatusID'
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-04-8
-- Description:	  添加视图：View_TopGuestRoomList
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_TopGuestRoomList]'))
begin
drop view [View_TopGuestRoomList];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_TopGuestRoomList]
AS
SELECT     dbo.View_GuestRoomList.*
FROM         dbo.View_TopGuestRoomID INNER JOIN
                      dbo.View_GuestRoomList ON dbo.View_TopGuestRoomID.GuestRoomID= dbo.View_GuestRoomList.GuestRoomID'
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-04-08
-- Description:	景点表添加字段
-- =============================================
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='Attractions' and b.xtype='u' and a.name='TelePhone')
begin

alter table Attractions add TelePhone nvarchar(50) null
end

-- =============================================
-- Author:		luohr
-- Create date: 2011-04-08
-- Description:	GuestRoomOrderItem表添加字段
-- =============================================
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='GuestRoomOrderItem' and b.xtype='u' and a.name='StartDate')
begin

alter table GuestRoomOrderItem add StartDate datetime null
end
if not exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='GuestRoomOrderItem' and b.xtype='u' and a.name='EndDate')
begin

alter table GuestRoomOrderItem add EndDate datetime null
end
if exists(select 1 from syscolumns a,sysobjects b  where a.id=b.id and b.name='GuestRoomOrderItem' and b.xtype='u' and a.name='OrderDate')
begin

alter table GuestRoomOrderItem drop column OrderDate
end
go

-- =============================================
-- Author:		luohr
-- Create date: 2011-05-06
-- Description:	  添加视图：View_TicketPackageList
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_TicketPackageList]'))
begin
drop view [View_TicketPackageList];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_TicketPackageList]
AS
SELECT     dbo.TicketPackage.*, dbo.View_AttractionsTicket.AttractionsID, dbo.View_AttractionsTicket.AttractionTypeID, 
                      dbo.View_AttractionsTicket.ProvinceID, dbo.View_AttractionsTicket.CityID, dbo.View_AttractionsTicket.AttractionStatus, 
                      dbo.View_AttractionsTicket.ApproveStatusID, dbo.View_AttractionsTicket.AttractionsName, dbo.View_AttractionsTicket.Address, 
                      dbo.View_AttractionsTicket.Introduction, dbo.View_AttractionsTicket.ImageUrl, dbo.View_AttractionsTicket.Description, 
                      dbo.View_AttractionsTicket.SiteUrl, dbo.View_AttractionsTicket.AttractionTypeName, dbo.View_AttractionsTicket.ProvinceName, 
                      dbo.View_AttractionsTicket.CityName, dbo.View_AttractionsTicket.Area, dbo.View_AttractionsTicket.TicketTypeID, 
                      dbo.View_AttractionsTicket.TicketsApproveStatusID, dbo.View_AttractionsTicket.TicketsName, dbo.View_AttractionsTicket.OriginalPrice, 
                      dbo.View_AttractionsTicket.SettlementPrice, dbo.View_AttractionsTicket.SalesGuidePrice, dbo.View_AttractionsTicket.Discount, 
                      dbo.View_AttractionsTicket.BackPoint, dbo.View_AttractionsTicket.TicketStatus, dbo.View_AttractionsTicket.TicketTypeName, 
                      dbo.View_AttractionsTicket.ApproveStatusName, dbo.View_AttractionsTicket.TicketsApproveStatusName, dbo.View_GuestRoomList.HotelInfoID, 
                      dbo.View_GuestRoomList.ApproveStatusID AS RoomApproveStatusID, dbo.View_GuestRoomList.GuestRoomName, 
                      dbo.View_GuestRoomList.OriginalPrice AS RoomOriginalPrice, dbo.View_GuestRoomList.WeekdaysPrice, dbo.View_GuestRoomList.WeekendPrice, 
                      dbo.View_GuestRoomList.HolidayPrice, dbo.View_GuestRoomList.Profit, dbo.View_GuestRoomList.GuestRoomStatus, 
                      dbo.View_GuestRoomList.Quantity, dbo.View_GuestRoomList.GuestRoomUrl, dbo.View_GuestRoomList.ProvinceID AS RoomProvinceID, 
                      dbo.View_GuestRoomList.CityID AS RoomCityID, dbo.View_GuestRoomList.HotelStatusID, dbo.View_GuestRoomList.HotelApproveStatusID, 
                      dbo.View_GuestRoomList.HotelName, dbo.View_GuestRoomList.Address AS HotelAddress, dbo.View_GuestRoomList.Introduction AS HotelIntroduction, 
                      dbo.View_GuestRoomList.ImageUrl AS HotelImageUrl, dbo.View_GuestRoomList.SiteUrl AS HotelSiteUrl, dbo.View_GuestRoomList.TelePhone, 
                      dbo.View_GuestRoomList.Fax, dbo.View_GuestRoomList.Description AS HotelDescription, dbo.View_GuestRoomList.CityName AS HotelCityName, 
                      dbo.View_GuestRoomList.ProvinceName AS HotelProvinceName, dbo.View_GuestRoomList.HotelApproveStatusName, 
                      dbo.View_GuestRoomList.ApproveStatusName AS RoomApproveStatusName
FROM         dbo.TicketPackage INNER JOIN
                      dbo.View_AttractionsTicket ON dbo.TicketPackage.TicketsID = dbo.View_AttractionsTicket.TicketsID INNER JOIN
                      dbo.View_GuestRoomList ON dbo.TicketPackage.GuestRoomID = dbo.View_GuestRoomList.GuestRoomID'
go

-- =============================================
-- Author:		liangzy
-- Create date: 2011-06-17
-- Description:	 修改视图：[View_GuestRoomStatus]
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_GuestRoomStatus]'))
begin
drop view [View_GuestRoomStatus];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_GuestRoomStatus]
AS
SELECT     dbo.GuestRoomStatus.*, dbo.RoomStatus.RoomStatusName
FROM         dbo.GuestRoomStatus INNER JOIN
                      dbo.RoomStatus ON dbo.GuestRoomStatus.RoomStatusID = dbo.RoomStatus.RoomStatusID'

-- =============================================
-- Author:		liangzy
-- Create date: 2011-06-23
-- Description:	 创建视图：[View_GuestRoomQty]
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_GuestRoomQty]'))
begin
drop view [View_GuestRoomQty];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_GuestRoomQty]
AS
SELECT     dbo.GuestRoomQty.*, dbo.GuestRoom.GuestRoomName, dbo.HotelInfo.HotelName
FROM         dbo.GuestRoomQty INNER JOIN
                      dbo.GuestRoom ON dbo.GuestRoomQty.GuestRoomID = dbo.GuestRoom.GuestRoomID INNER JOIN
                      dbo.HotelInfo ON dbo.GuestRoom.HotelInfoID = dbo.HotelInfo.HotelInfoID'

-- =============================================
-- Author:		liangzy
-- Create date: 2011-06-23
-- Description:	 修改视图：[View_OrderItem]
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_OrderItem]'))
begin
drop view [View_OrderItem];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_OrderItem]
AS
SELECT     dbo.TicketOrderItems.TicketOrderItemsID, dbo.TicketOrderItems.OrdersID, dbo.TicketOrderItems.TicketsID, dbo.TicketOrderItems.AttractionsID, 
                      dbo.TicketOrderItems.TicketTypeID, dbo.TicketOrderItems.TicketTypeName, dbo.TicketOrderItems.TicketsName, dbo.TicketOrderItems.OriginalPrice, 
                      dbo.TicketOrderItems.SettlementPrice, dbo.TicketOrderItems.SalesGuidePrice, dbo.TicketOrderItems.Discount, dbo.TicketOrderItems.BackPoint, 
                      dbo.TicketOrderItems.Quantity, dbo.TicketOrderItems.Tax, dbo.TicketOrderItems.TotalAmount, dbo.TicketOrderItems.Creater, dbo.TicketOrderItems.CreatedBy, 
                      dbo.TicketOrderItems.Created, dbo.TicketOrderItems.Modified, dbo.TicketOrderItems.ModifiedBy, dbo.TicketOrderItems.ModifiedByID, dbo.View_Order.MembersID, 
                      dbo.View_Order.MemberName, dbo.View_Order.Contact, dbo.View_Order.OrderStatusID, dbo.View_Order.OrdersNo, dbo.View_Order.OrderTypeID, 
                      dbo.View_Order.OrderDate, dbo.View_Order.Description, dbo.View_Order.TotalAmount AS OrderTotalAmount, dbo.View_Order.OrderTypeName, 
                      dbo.View_Order.OrderStatusName, dbo.View_Order.Created AS OrderCreated, dbo.Attractions.AttractionsName, dbo.TicketOrderItems.ContactPhone, 
                      dbo.TicketOrderItems.Visitors, dbo.TicketOrderItems.CustomerName, dbo.TicketOrderItems.ItemOrdersNo
FROM         dbo.TicketOrderItems INNER JOIN
                      dbo.Attractions ON dbo.TicketOrderItems.AttractionsID = dbo.Attractions.AttractionsID INNER JOIN
                      dbo.View_Order ON dbo.TicketOrderItems.OrdersID = dbo.View_Order.OrdersID'


-- =============================================
-- Author:		liangzy
-- Create date: 2011-06-23
-- Description:	 创建视图：[View_GuestRoomOrderItem]
-- =============================================
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[View_GuestRoomOrderItem]'))
begin
drop view [View_GuestRoomOrderItem];
end
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[View_GuestRoomOrderItem]
AS
SELECT     dbo.GuestRoomOrderItem.*, dbo.Orders.OrdersNo, dbo.OrderStatus.OrderStatusName
FROM         dbo.GuestRoomOrderItem INNER JOIN
                      dbo.Orders ON dbo.GuestRoomOrderItem.OrdersID = dbo.Orders.OrdersID INNER JOIN
                      dbo.OrderStatus ON dbo.Orders.OrderStatusID = dbo.OrderStatus.OrderStatusID'

